﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace StudentManagementSystem
{
    namespace StudentManagementSystem
    {
        public class DatabaseManager
        {
            private string connectionString = "Data Source=StudentManagement.db;Version=3;";

            public void InsertSchool(School school)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "INSERT INTO Schools (Name, Address) VALUES (@Name, @Address)";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", school.Name);
                        command.Parameters.AddWithValue("@Address", school.Address);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Inserted School: {school.Name}");
            }

            public void InsertClass(Class @class)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "INSERT INTO Classes (Name) VALUES (@Name)";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", @class.Name);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Inserted Class: {@class.Name}");
            }

            public void InsertStudent(Student student)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "INSERT INTO Students (Name, Age, Gender, Class) VALUES (@Name, @Age, @Gender, @Class)";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", student.Name);
                        command.Parameters.AddWithValue("@Age", student.Age);
                        command.Parameters.AddWithValue("@Gender", student.Gender);
                        command.Parameters.AddWithValue("@Class", student.Class);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Inserted Student: {student.Name}");
            }

            public void DeleteSchool(string schoolName)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "DELETE FROM Schools WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", schoolName);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Deleted School: {schoolName}");
            }

            public void DeleteClass(string className)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "DELETE FROM Classes WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", className);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Deleted Class: {className}");
            }

            public void DeleteStudent(string studentName)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "DELETE FROM Students WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", studentName);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Deleted Student: {studentName}");
            }

            public void UpdateSchool(string schoolName, School newSchoolData)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "UPDATE Schools SET Name = @NewName, Address = @NewAddress WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@NewName", newSchoolData.Name);
                        command.Parameters.AddWithValue("@NewAddress", newSchoolData.Address);
                        command.Parameters.AddWithValue("@Name", schoolName);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Updated School: {schoolName}");
            }

            public void UpdateClass(string className, Class newClassData)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "UPDATE Classes SET Name = @NewName WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@NewName", newClassData.Name);
                        command.Parameters.AddWithValue("@Name", className);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Updated Class: {className}");
            }

            public void UpdateStudent(string studentName, Student newStudentData)
            {
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "UPDATE Students SET Name = @NewName, Age = @NewAge, Gender = @NewGender, Class = @NewClass WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))

                    {
                        command.Parameters.AddWithValue("@NewName", newStudentData.Name);
                        command.Parameters.AddWithValue("@NewAge", newStudentData.Age);
                        command.Parameters.AddWithValue("@NewGender", newStudentData.Gender);
                        command.Parameters.AddWithValue("@NewClass", newStudentData.Class);
                        command.Parameters.AddWithValue("@Name", studentName);

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine($"Updated Student: {studentName}");
            }

            public School QuerySchool(string schoolName)
            {
                School school = new School();
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "SELECT * FROM Schools WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", schoolName);

                        using (SqliteDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                school.Name = reader["Name"].ToString();
                                school.Address = reader["Address"].ToString();
                            }
                        }
                    }
                }
                return school;
            }

            public Class QueryClass(string className)
            {
                Class @class = new Class();
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "SELECT * FROM Classes WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", className);

                        using (SqliteDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                @class.Name = reader["Name"].ToString();
                            }
                        }
                    }
                }
                return @class;
            }

            public Student QueryStudent(string studentName)
            {
                Student student = new Student();
                using (SqliteConnection connection = new SqliteConnection(connectionString))
                {
                    connection.Open();
                    string query = "SELECT * FROM Students WHERE Name = @Name";

                    using (SqliteCommand command = new SqliteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Name", studentName);

                        using (SqliteDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                student.Name = reader["Name"].ToString();
                                student.Age = int.Parse(reader["Age"].ToString());
                                student.Gender = reader["Gender"].ToString();
                                student.Class = reader["Class"].ToString();
                            }
                        }
                    }
                }
                return student;
            }
        }
    }
}
